package com.xing.lei.happy.thrid.flh.sql.service;

import com.xing.lei.happy.thrid.flh.sql.enums.SqlType;
import com.xing.lei.happy.thrid.flh.sql.model.CreateTableParts;
import com.xing.lei.happy.thrid.flh.sql.model.InsertStatement;
import com.xing.lei.happy.thrid.flh.sql.utils.SqlTypeRecognizer;
import com.xing.lei.happy.thrid.flh.sql.utils.SqlUtils;

import java.util.*;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

/**
 * Oracle数据库SQL转换器
 * 负责将Oracle数据库的SQL语句转换为Oracle数据库可重复执行sql格式
 */
public class OracleSqlConverter implements SqlTypeConverter {
    /**
     * 转换SQL语句数组为Oracle数据库格式
     * @param sqlList 需要转换的SQL语句数组
     * @return 转换后的SQL语句
     */
    @Override
    public String convert(List<String> sqlList) {
        Map<SqlType, List<String>> typeMap = new LinkedHashMap<>();
        for (String sql : sqlList) {
            for (String part : sql.split(";")) {
                String trimmed = part.trim();
                if (!trimmed.isEmpty()) {
                    SqlType type = SqlTypeRecognizer.recognize(trimmed);
                    typeMap.computeIfAbsent(type, k -> new ArrayList<>()).add(trimmed);
                }
            }
        }

        StringBuilder sb = new StringBuilder();
        sb.append("\n-- Oracle可重复执行脚本\n");
        sb.append("DECLARE\n    V_FLAG NUMBER := 0;\nBEGIN\n");
        
        // 处理建表语句
        List<String> createTableList = typeMap.getOrDefault(SqlType.CREATE_TABLE, Collections.emptyList());
        if (!createTableList.isEmpty()) {
            sb.append("    -- 创建表\n");
            for (String sql : createTableList) {
                CreateTableParts parts = SqlUtils.parseCreateTableParts(sql, "Oracle");
                String table = parts.getTableName();
                sb.append("    SELECT COUNT(1) INTO V_FLAG FROM USER_TABLES WHERE TABLE_NAME='").append(table.toUpperCase()).append("';\n");
                sb.append("    IF (V_FLAG = 0) THEN\n");
                sb.append("        BEGIN\n");
                // CREATE TABLE
                sb.append("            EXECUTE IMMEDIATE \n");
                sb.append(SqlUtils.formatOracleMultilineSql(parts.getCreateTableSql(), 12)).append(";\n");
                // 主键
                if (parts.getPrimaryKeySql() != null) {
                    sb.append("            -- 创建主键\n");
                    sb.append("            EXECUTE IMMEDIATE \n");
                    sb.append(SqlUtils.formatOracleMultilineSql(parts.getPrimaryKeySql(), 12)).append(";\n");
                }
                // 索引
                for (String idx : parts.getIndexSqls()) {
                    sb.append("            -- 创建索引\n");
                    sb.append("            EXECUTE IMMEDIATE \n");
                    sb.append(SqlUtils.formatOracleMultilineSql(idx, 12)).append(";\n");
                }
                sb.append("        END;\n");
                sb.append("    END IF;\n");
            }
            sb.append("\n");
        }

        // 处理新增列
        List<String> addColList = typeMap.getOrDefault(SqlType.ADD_COLUMN, Collections.emptyList());
        if (!addColList.isEmpty()) {
            sb.append("    -- 新增列\n");
            for (String sql : addColList) {
                String table = parseTableNameFromAlter(sql);
                String column = parseColumnNameFromAlter(sql);
                sb.append("    SELECT COUNT(1) INTO V_FLAG FROM USER_TAB_COLUMNS WHERE UPPER(COLUMN_NAME)='")
                  .append(column.toUpperCase()).append("' AND TABLE_NAME = '").append(table.toUpperCase()).append("';\n");
                sb.append("    IF V_FLAG = 0 THEN\n");
                sb.append("        EXECUTE IMMEDIATE '").append(sql.replaceAll("ALTER\\s+TABLE", "ALTER TABLE")).append("';\n");
                sb.append("    END IF;\n");
            }
            sb.append("\n");
        }

        // 处理修改列
        List<String> modifyColList = typeMap.getOrDefault(SqlType.MODIFY_COLUMN, Collections.emptyList());
        if (!modifyColList.isEmpty()) {
            sb.append("    -- 修改列\n");
            for (String sql : modifyColList) {
                sb.append("    EXECUTE IMMEDIATE '").append(sql.trim().replaceAll("ALTER\\s+TABLE", "ALTER TABLE")).append("';\n");
            }
            sb.append("\n");
        }

        // 处理新增行
        List<String> insertList = typeMap.getOrDefault(SqlType.INSERT, Collections.emptyList());
        if (!insertList.isEmpty()) {
            // 1. 按表名分组
            Map<String, List<InsertStatement>> tableGroups = new LinkedHashMap<>();
            // 2. 拆分每条insert语句
            List<String> singleInserts = new ArrayList<>();
            for (String sql : insertList) {
                for (String part : sql.split(";")) {
                    String trimmed = part.trim();
                    if (SqlType.INSERT.equals(SqlTypeRecognizer.recognize(trimmed))) {
                        singleInserts.add(trimmed);
                    }
                }
            }
            // 3. 解析并分组
            for (String sql : singleInserts) {
                InsertStatement stmt = SqlUtils.parseSingleInsert(sql);
                tableGroups.computeIfAbsent(stmt.getTable(), k -> new ArrayList<>()).add(stmt);
            }
            // 4. 每个表生成一个新增行脚本
            for (Map.Entry<String, List<InsertStatement>> entry : tableGroups.entrySet()) {
                String table = entry.getKey();
                List<InsertStatement> stmts = entry.getValue();
                sb.append("    --新增行 ").append(table).append("\n");
                List<String> ids = new ArrayList<>();
                for (InsertStatement s : stmts) ids.add("'" + s.getId() + "'");
                sb.append("    SELECT COUNT(1) INTO V_FLAG FROM ").append(table).append(" WHERE " + stmts.get(0).getIdName() + " IN (").append(String.join(",", ids)).append(");\n");
                sb.append("    IF (V_FLAG = 0) THEN\n");
                sb.append("        BEGIN\n");
                for (InsertStatement s : stmts) {
                    sb.append("            INSERT INTO ").append(s.getTable())
                      .append(" (").append(s.getColumns()).append(")\n")
                      .append("            VALUES (").append(s.getValues()).append(");\n");
                }
                sb.append("        END;\n");
                sb.append("    END IF;\n");
                sb.append("\n");
            }
            sb.append("    COMMIT;\n");
        }
        sb.append("END;\n/");
        return sb.toString();
    }

    private String parseTableNameFromAlter(String sql) {
        Pattern pattern = Pattern.compile("ALTER\\s+TABLE\\s+([a-zA-Z0-9_]+)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            return matcher.group(1);
        }
        throw new IllegalArgumentException("无法解析表名: " + sql);
    }

    private String parseColumnNameFromAlter(String sql) {
        Pattern pattern = Pattern.compile("ADD\\s+([a-zA-Z0-9_]+)", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        if (matcher.find()) {
            return matcher.group(1);
        }
        throw new IllegalArgumentException("无法解析字段名: " + sql);
    }
} 